package com.shlh.saas.service.facebook.impl;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.shlh.saas.entity.facebook.FacebookUser;
import com.shlh.saas.entity.facebook.FacebookUserEs;
import com.shlh.saas.mapper.facebook.FacebookUserMapper;
import com.shlh.saas.model.dto.FacebookQueryDTO;
import com.shlh.saas.service.facebook.FacebookEsService;
import com.shlh.saas.service.facebook.FacebookUserService;
import com.shlh.saas.util.FaceBookDataSource;
import com.shlh.saas.util.ThreadPoolFactory;
import jakarta.annotation.Resource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.MapUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.util.*;
import java.util.concurrent.*;
import java.util.stream.Collectors;


/**
 * Facebook用户服务实现类
 */
@Service
@Slf4j
public class FacebookUserServiceImpl extends ServiceImpl<FacebookUserMapper, FacebookUser> implements FacebookUserService {

    @Autowired
    private FacebookUserMapper facebookUserMapper;

    @Resource
    private FacebookEsService facebookEsService;

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private FaceBookDataSource faceBookDataSource;

    @Override
    @Transactional(readOnly = true, timeout = 60)
    public Page<FacebookUser> getFacebookDataPage(FacebookQueryDTO queryDTO) {
        log.debug("开始分页查询Facebook数据: pageNum={}, pageSize={}", queryDTO.getPageNum(), queryDTO.getPageSize());
        long startTime = System.currentTimeMillis();

        // 参数验证
        if (queryDTO == null || queryDTO.getPageNum() <= 0 || queryDTO.getPageSize() <= 0) {
            log.warn("分页查询参数无效: {}", queryDTO);
            Page<FacebookUser> emptyPage = new Page<>(1, 10);
            emptyPage.setTotal(0);
            emptyPage.setRecords(new ArrayList<>());
            return emptyPage;
        }

        // 限制分页大小，防止大查询导致连接超时
        int maxPageSize = 1000;
        if (queryDTO.getPageSize() > maxPageSize) {
            log.warn("分页大小超过限制，调整为最大值: {} -> {}", queryDTO.getPageSize(), maxPageSize);
            queryDTO.setPageSize(maxPageSize);
        }

        try {
            FacebookUserEs facebookUserEs = convertToFacebookEs(queryDTO);

            Page<FacebookUserEs> facebookUserEsPage = facebookEsService.queryFacebookUserList(facebookUserEs);

            Page<FacebookUser> facebookUserPage;
            facebookUserPage = convertToFacebookUserPag(facebookUserEsPage);

            return facebookUserPage;
        } catch (Exception e) {
            log.error("分页查询Facebook数据失败: pageNum={}, pageSize={}, error={}, 耗时={}ms",
                    queryDTO.getPageNum(), queryDTO.getPageSize(), e.getMessage(),
                    System.currentTimeMillis() - startTime, e);

            // 返回空页，确保不会因为异常导致连接泄漏
            Page<FacebookUser> emptyPage = new Page<>(queryDTO.getPageNum(), queryDTO.getPageSize());
            emptyPage.setTotal(0);
            emptyPage.setRecords(new ArrayList<>());
            return emptyPage;
        }
    }

    private Page<FacebookUser> convertToFacebookUserPag(Page<FacebookUserEs> facebookUserEsPage) {

        Page<FacebookUser> facebookUserPage = new Page<>();

        facebookUserPage.setCurrent(facebookUserEsPage.getCurrent());
        facebookUserPage.setSize(facebookUserEsPage.getSize());
        List<FacebookUser> facebookUserList = new LinkedList<>();
        for (FacebookUserEs facebookUserEs : facebookUserEsPage.getRecords()) {
            FacebookUser facebookUser = new FacebookUser();
            facebookUser = convertToFacebookUser(facebookUserEs);
            if (Objects.nonNull(facebookUser)) {
                facebookUserList.add(facebookUser);
            }
        }
        facebookUserPage.setRecords(facebookUserList);
        facebookUserPage.setTotal(facebookUserEsPage.getTotal());
        return facebookUserPage;
    }

    private FacebookUser convertToFacebookUser(FacebookUserEs facebookUserEs) {
        FacebookUser facebookUser = new FacebookUser();

        // 基本信息
        if (facebookUserEs.getMysqlId() != null){
            facebookUser.setId(facebookUserEs.getMysqlId());
        }

        if (facebookUserEs.getUid() != null) {
            facebookUser.setFacebookId(facebookUserEs.getUid());
        }

        if (facebookUserEs.getFirstName() != null) {
            facebookUser.setFirstName(facebookUserEs.getFirstName());
        }

        if (facebookUserEs.getLastName() != null) {
            facebookUser.setLastName(facebookUserEs.getLastName());
        }

        if (facebookUserEs.getFullName() != null) {
            facebookUser.setFullName(facebookUserEs.getFullName());
        }

        if (facebookUserEs.getGender() != null) {
            facebookUser.setGender(facebookUserEs.getGender());
        }

        if (facebookUserEs.getEmail() != null) {
            facebookUser.setEmail(facebookUserEs.getEmail());
        }

        if (facebookUserEs.getPhoneNumber() != null) {
            facebookUser.setPhoneNumber(facebookUserEs.getPhoneNumber());
        }

        // 位置信息
        if (facebookUserEs.getWorkplaceCity() != null) {
            facebookUser.setCurrentLocation(facebookUserEs.getWorkplaceCity());
        }

        if (facebookUserEs.getBirthPlace() != null) {
            facebookUser.setHometown(facebookUserEs.getBirthPlace());
        }

        if (facebookUserEs.getWorkPlace() != null) {
            facebookUser.setWorkplace(facebookUserEs.getWorkPlace());
        }

        if (facebookUserEs.getMaritalStatus() != null){
            facebookUser.setRelationshipStatus(facebookUserEs.getMaritalStatus());
        }

        // 数据源信息
        facebookUser.setMysqlTable(facebookUserEs.getMysqlTable());

        // 设置导出需要的其他字段（这些字段在当前ES结构中不存在，设置为默认值）
        // 如果需要这些字段的真实数据，需要从其他数据源获取或扩展ES索引
        facebookUser.setVerified(null);
        facebookUser.setFriendsCount(null);
        facebookUser.setFollowersCount(null);
        facebookUser.setPostsCount(null);
        facebookUser.setProfilePictureUrl(null);
        facebookUser.setBio(null);
        facebookUser.setLastActiveTime(null);
        facebookUser.setRegistrationTime(null);
        facebookUser.setCreatedAt(null);

        return facebookUser;
    }

    private FacebookUserEs convertToFacebookEs(FacebookQueryDTO queryDTO) {
        FacebookUserEs facebookUserEs = new FacebookUserEs();

        if (queryDTO.getFacebookId() != null) {
            facebookUserEs.setFacebookId(queryDTO.getFacebookId());
        }

        if (queryDTO.getFirstName() != null) {
            facebookUserEs.setFirstName(queryDTO.getFirstName());
        }

        if (queryDTO.getLastName() != null) {
            facebookUserEs.setLastName(queryDTO.getLastName());
        }

        if (queryDTO.getName() != null) {
            facebookUserEs.setFullName(queryDTO.getName());
        }

        if (queryDTO.getGender() != null) {
            if ("M".equals(queryDTO.getGender())) {
                facebookUserEs.setGender("male");
            } else {
                facebookUserEs.setGender("female");
            }
        }

        if (queryDTO.getEmail() != null) {
            facebookUserEs.setEmail(queryDTO.getEmail());
        }

        if (queryDTO.getPhoneNumber() != null) {
            facebookUserEs.setPhoneNumber(queryDTO.getPhoneNumber());
        }

        if (queryDTO.getCurrentLocation() != null) {
            facebookUserEs.setWorkplaceCity(queryDTO.getCurrentLocation());
        }

        if (queryDTO.getHometown() != null) {
            facebookUserEs.setBirthPlace(queryDTO.getHometown());
        }

        if (queryDTO.getContactMethod() != null) {
            facebookUserEs.setContactMethod(queryDTO.getContactMethod());
        }

        if (queryDTO.getWorkplace() != null) {
            facebookUserEs.setWorkPlace(queryDTO.getWorkplace());
        }

        if (queryDTO.getRelationshipStatus() != null) {
            facebookUserEs.setMaritalStatus(queryDTO.getRelationshipStatus());
        }

        // 页码
        if (queryDTO.getPageNum() != null) {
            facebookUserEs.setCurrent(queryDTO.getPageNum());
        }

        // 条数
        if (queryDTO.getPageSize() != null) {
            facebookUserEs.setSize(queryDTO.getPageSize());
        }


        return facebookUserEs;
    }


    @Override
    public List<FacebookUser> exportFacebookData(FacebookQueryDTO queryDTO) {

        Page<FacebookUserEs> facebookUserEsPage = facebookEsService.queryFacebookUserList(convertToFacebookEs(queryDTO));

        return convertToFacebookUserPag(facebookUserEsPage).getRecords();

    }

    @Override
    public List<String> getSuggestions(String keyword) {
        log.info("获取Facebook数据搜索建议，关键词: {}", keyword);
        if (!StringUtils.hasText(keyword) || keyword.length() < 2) {
            return new ArrayList<>();
        }

        try {
            // 从姓名中查找
            List<String> names = facebookUserMapper.findSimilarNames(keyword);

            // 从位置中查找
            List<String> locations = facebookUserMapper.findSimilarLocations(keyword);

            // 合并结果并去重
            List<String> suggestions = new ArrayList<>();
            suggestions.addAll(names);
            suggestions.addAll(locations);

            // 去重并限制数量
            return suggestions.stream()
                    .distinct()
                    .limit(10)
                    .toList();
        } catch (Exception e) {
            log.error("获取Facebook数据搜索建议失败", e);
            return new ArrayList<>();
        }
    }

    /**
     * 使用筛选条件搜索Facebook联系人
     */
    @Override
    @Transactional(readOnly = true, timeout = 60)
    public Page<FacebookUser> searchWithFilters(FacebookQueryDTO queryDTO, int offset, int limit) {
        log.debug("使用筛选条件搜索Facebook联系人: 关键词={}, 性别={}, 当前位置={}, 家乡={}, 关系状态={}, 工作地点={}, 联系方式={}, 偏移量={}, 限制数={}",
                queryDTO.getName(), queryDTO.getGender(), queryDTO.getCurrentLocation(),
                queryDTO.getHometown(), queryDTO.getRelationshipStatus(), queryDTO.getWorkplace(),
                queryDTO.getContactMethod(), offset, limit);

        // 参数验证和调整
        if (queryDTO == null) {
            log.warn("查询参数为空");
            return new Page<>();
        }

        if (offset < 0) {
            log.warn("偏移量不能为负数，调整为0: {}", offset);
            offset = 0;
        }

        if (limit <= 0 || limit > 1000) {
            int originalLimit = limit;
            limit = Math.min(Math.max(limit, 1), 1000);
            log.warn("限制数无效，调整为合理范围: {} -> {}", originalLimit, limit);
        }

        try {
            FacebookUserEs facebookUserEs = convertToFacebookEs(queryDTO);
            Page<FacebookUserEs> facebookUserEsPage = facebookEsService.queryFacebookUserList(facebookUserEs);

            Page<FacebookUser> facebookUserPage;
            facebookUserPage = convertToFacebookUserPag(facebookUserEsPage);

            return facebookUserPage;

        } catch (Exception e) {
            log.error("使用筛选条件搜索Facebook联系人出错: queryDTO={}, offset={}, limit={}, error={}",
                    queryDTO, offset, limit, e.getMessage(), e);
            return new Page<>();
        }
    }


    /**
     * 获取符合条件的Facebook数据数量
     *
     * @param queryDTO 查询参数
     * @return 符合条件的记录数
     */
    @Override
    public long countFacebookData(FacebookQueryDTO queryDTO) {
        log.debug("计算符合条件的Facebook数据数量，参数: {}", queryDTO);

        // DTO参数验证
        if (queryDTO == null) {
            log.warn("查询参数为空");
            return 0;
        }

        try {
            long count;

            count = facebookEsService.countForExport(queryDTO);

            return count;

        } catch (Exception e) {
            log.error("计算符合条件的Facebook数据数量失败: queryDTO={}, error={}",
                    queryDTO, e.getMessage(), e);
            return 0;
        }
    }


    @Override
    public FacebookUser matchByNameAndWorkplace(String firstName, String lastName, String workplace) {
        log.info("执行企业+姓名匹配邮箱: firstName={}, lastName={}, workplace={}", firstName, lastName, workplace);

        // 构建查询条件
        LambdaQueryWrapper<FacebookUser> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(FacebookUser::getFirstName, firstName);
        queryWrapper.eq(FacebookUser::getLastName, lastName);
        queryWrapper.eq(FacebookUser::getWorkplace, workplace);
        queryWrapper.isNotNull(FacebookUser::getEmail);

        // 查询匹配记录
        return facebookUserMapper.selectOne(queryWrapper);
    }

    @Override
    public FacebookUser matchPhoneByNameAndWorkplace(String firstName, String lastName, String workplace) {
        log.info("执行企业+姓名匹配手机号: firstName={}, lastName={}, workplace={}", firstName, lastName, workplace);

        // 构建查询条件
        LambdaQueryWrapper<FacebookUser> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(FacebookUser::getFirstName, firstName);
        queryWrapper.eq(FacebookUser::getLastName, lastName);
        queryWrapper.eq(FacebookUser::getWorkplace, workplace);
        queryWrapper.isNotNull(FacebookUser::getPhoneNumber);

        // 查询匹配记录
        return facebookUserMapper.selectOne(queryWrapper);
    }

    @Override
    public FacebookUser matchPhoneByNameAndEmail(String firstName, String lastName, String email) {
        log.info("执行姓名+邮箱匹配手机号: firstName={}, lastName={}, email={}", firstName, lastName, email);

        // 构建查询条件
        LambdaQueryWrapper<FacebookUser> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(FacebookUser::getFirstName, firstName);
        queryWrapper.eq(FacebookUser::getLastName, lastName);
        queryWrapper.eq(FacebookUser::getEmail, email);
        queryWrapper.isNotNull(FacebookUser::getPhoneNumber);

        // 查询匹配记录
        return facebookUserMapper.selectOne(queryWrapper);
    }

    @Override
    public FacebookUser matchEmailByPhoneAndName(String phoneNumber, String firstName, String lastName) {
        log.info("执行手机号+姓名匹配邮箱: phoneNumber={}, firstName={}, lastName={}", phoneNumber, firstName, lastName);

        // 构建查询条件
        LambdaQueryWrapper<FacebookUser> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(FacebookUser::getPhoneNumber, phoneNumber);
        queryWrapper.eq(FacebookUser::getFirstName, firstName);
        queryWrapper.eq(FacebookUser::getLastName, lastName);
        queryWrapper.isNotNull(FacebookUser::getEmail);

        // 查询匹配记录
        return facebookUserMapper.selectOne(queryWrapper);
    }

    @Override
    public Map<String, List<String>> getFacebookMapList() {
        Map<String, List<String>> listMap = new HashMap<>();

        FacebookQueryDTO queryDTO = new FacebookQueryDTO();

        int page = 10;
        int size = 1000;
        queryDTO.setPageNum(page);
        queryDTO.setPageSize(size);

        FacebookUserEs facebookUserEs = convertToFacebookEs(queryDTO);

        Map<String, List<String>> facebookUserMapList = facebookEsService.queryFacebookUserMapList(facebookUserEs);

        listMap.put("currentLocations", facebookUserMapList.get("currentLocations"));
        listMap.put("hometowns", facebookUserMapList.get("hometowns"));
        listMap.put("relationshipStatuses", facebookUserMapList.get("relationshipStatuses"));
        listMap.put("workplaces", facebookUserMapList.get("workplaces"));

        return listMap;
    }

    /**
     * 根据数据表和Facebook ID获取联系人详情
     *
     * @param mysqlTable 数据表名
     * @param id Facebook ID
     * @return Facebook联系人信息
     */
    @Override
    public FacebookUser getFacebookDetailByTableAndId(String mysqlTable, String id) {
        try {
            log.info("根据数据表和Facebook ID获取联系人详情: mysqlTable={}, facebookId={}", mysqlTable, id);

            // 参数验证
            if (!StringUtils.hasText(mysqlTable) || !StringUtils.hasText(id)) {
                log.warn("参数不能为空: mysqlTable={}, id={}", mysqlTable, id);
                return null;
            }

            // 验证数据表名是否安全（防止SQL注入）
            if (!isValidTableName(mysqlTable)) {
                log.warn("无效的数据表名: {}", mysqlTable);
                return null;
            }

            // 使用ShardingSphere兼容的查询方式
            List<Map<String, Object>> results = executeShardingSphereCompatibleQuery(mysqlTable, id);

            if (results.isEmpty()) {
                log.info("未找到对应的联系人信息: mysqlTable={}, id={}", mysqlTable, id);
                return null;
            }

            // 将查询结果转换为FacebookUser对象
            Map<String, Object> row = results.get(0);
            FacebookUser facebookUser = convertMapToFacebookUser(row, mysqlTable);

            log.info("成功获取联系人详情: {}",
                    JSON.toJSONString(facebookUser));

            return facebookUser;

        } catch (Exception e) {
            log.error("获取联系人详情失败: mysqlTable={}, facebookId={}, error={}",
                     mysqlTable, id, e.getMessage(), e);
            throw new RuntimeException("获取联系人详情失败: " + e.getMessage(), e);
        }
    }

    public Map<String, Object> getFacebookByTableAndId(String mysqlTable, String id) {
        try {
            log.info("根据数据表和Facebook ID获取联系人详情: mysqlTable={}, facebookId={}", mysqlTable, id);

            // 参数验证
            if (!StringUtils.hasText(mysqlTable) || !StringUtils.hasText(id)) {
                log.warn("参数不能为空: mysqlTable={}, id={}", mysqlTable, id);
                return null;
            }

            // 验证数据表名是否安全（防止SQL注入）
            if (!isValidTableName(mysqlTable)) {
                log.warn("无效的数据表名: {}", mysqlTable);
                return null;
            }

            // 使用ShardingSphere兼容的查询方式
            List<Map<String, Object>> results = executeShardingSphereCompatibleQuery(mysqlTable, id);

            if (results.isEmpty()) {
                log.info("未找到对应的联系人信息: mysqlTable={}, id={}", mysqlTable, id);
                return null;
            }

            return results.get(0);

        } catch (Exception e) {
            log.error("获取联系人详情失败: mysqlTable={}, facebookId={}, error={}",
                     mysqlTable, id, e.getMessage(), e);
            throw new RuntimeException("获取联系人详情失败: " + e.getMessage(), e);
        }
    }
    
    /**
     * 批量获取Facebook详情（按表名和ID列表）
     * 
     * @param mysqlTable 数据表名
     * @param ids ID列表
     * @return Facebook用户列表
     */
    private List<FacebookUser> batchGetFacebookDetailByTableAndIds(String mysqlTable, List<String> ids) {
        long startTime = System.currentTimeMillis();
        log.info("批量查询表{}: 包含{}条记录", mysqlTable, ids.size());
        
        try {
            // 验证表名安全
            if (!isValidTableName(mysqlTable)) {
                log.warn("无效的数据表名: {}", mysqlTable);
                return new ArrayList<>();
            }
            
            if (ids == null || ids.isEmpty()) {
                return new ArrayList<>();
            }
            
            // 分批查询（每批最多1000条，避免SQL过长）
            int batchSize = 1000;
            List<FacebookUser> allResults = new ArrayList<>();
            
            for (int i = 0; i < ids.size(); i += batchSize) {
                int end = Math.min(i + batchSize, ids.size());
                List<String> batchIds = ids.subList(i, end);
                
                // 构建IN查询的占位符
                String placeholders = String.join(",", Collections.nCopies(batchIds.size(), "?"));
                String sql = String.format(
                    "/* ShardingSphere hint: dataSourceName=facebook_txt */ SELECT * FROM facebook_txt.%s WHERE id IN (%s)",
                    mysqlTable, placeholders
                );
                
                log.debug("执行批量查询: 表={}, 条数={}, SQL长度={}", mysqlTable, batchIds.size(), sql.length());
                
                // 执行查询
                jdbcTemplate.setDataSource(faceBookDataSource.dataSource());
                List<Map<String, Object>> results = jdbcTemplate.queryForList(sql, batchIds.toArray());
                
                // 转换结果
                for (Map<String, Object> row : results) {
                    FacebookUser user = convertMapToFacebookUser(row, mysqlTable);
                    if (user != null) {
                        allResults.add(user);
                    }
                }
            }
            
            long endTime = System.currentTimeMillis();
            log.info("批量查询表{}完成: 请求{}条, 成功{}条, 耗时{}ms", 
                     mysqlTable, ids.size(), allResults.size(), (endTime - startTime));
            
            return allResults;
            
        } catch (Exception e) {
            log.error("批量查询表{}失败: ids数量={}", mysqlTable, ids.size(), e);
            return new ArrayList<>();
        }
    }

    /**
     * 执行ShardingSphere兼容的查询
     *
     * @param mysqlTable 数据表名
     * @param facebookId Facebook ID
     * @return 查询结果
     */
    private List<Map<String, Object>> executeShardingSphereCompatibleQuery(String mysqlTable, String facebookId) {
        try {
            List<Map<String, Object>> maps = executeWithFallback(mysqlTable, facebookId);

            if (!maps.isEmpty()){
                log.info("成功获取联系人列表: {}", maps);
                return maps;
            }else {
                log.warn("联系人列表为空");
                return new ArrayList<>();
            }

        } catch (Exception e) {
            log.warn("ShardingSphere查询失败，尝试备用方法: mysqlTable={}, facebookId={}, error={}",
                    mysqlTable, facebookId, e.getMessage());
            return new ArrayList<>();
        }
    }

    /**
     * 查询方法
     *
     * @param mysqlTable 数据表名
     * @param facebookId Facebook ID
     * @return 查询结果
     */
    private List<Map<String, Object>> executeWithFallback(String mysqlTable, String facebookId) {
        try {
            jdbcTemplate.setDataSource(faceBookDataSource.dataSource());
            // 使用ShardingSphere Hint强制指定数据源
            String sql =
                    "/* ShardingSphere hint: dataSourceName=facebook_txt */ " +
                    "SELECT * FROM facebook_txt." + mysqlTable + " WHERE id = ?";
            log.debug("执行备用查询: {}, 参数: {}", sql, facebookId);

            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, facebookId);

            return maps;

        } catch (Exception e) {
            log.error("所有查询方法都失败: mysqlTable={}, facebookId={}, error={}",
                     mysqlTable, facebookId, e.getMessage());
            throw new RuntimeException("查询失败: " + e.getMessage(), e);
        }
    }

    /**
     * 验证数据表名是否安全
     *
     * @param tableName 数据表名
     * @return 是否安全
     */
    private boolean isValidTableName(String tableName) {
        // 只允许字母、数字、下划线，防止SQL注入
        return tableName.matches("^[a-zA-Z0-9_]+$") && tableName.startsWith("data_");
    }

    /**
     * 将Map转换为FacebookUser对象
     *
     * @param row 数据行
     * @param mysqlTable 数据表名
     * @return FacebookUser对象
     */
    private FacebookUser convertMapToFacebookUser(Map<String, Object> row, String mysqlTable) {
        FacebookUser facebookUser = new FacebookUser();

        // 设置基本字段
        facebookUser.setId(getString(row, "id"));
        facebookUser.setFacebookId(getString(row, "uid"));
        facebookUser.setFirstName(getString(row, "first_name"));
        facebookUser.setLastName(getString(row, "last_name"));
        if (Objects.nonNull(row.get("firth_name")) && Objects.nonNull(row.get("last_name")) && Objects.nonNull(row.get("email")) && Objects.nonNull(row.get("phone_number"))) {
            String firstName = getString(row, "first_name");
            String lastName = getString(row, "last_name");
            String fullName = firstName + " " + lastName;
            facebookUser.setFullName(fullName);
        }
        facebookUser.setEmail(getString(row, "email"));
        facebookUser.setPhoneNumber(getString(row, "phone_number"));
        facebookUser.setGender(getString(row, "gender"));
        facebookUser.setCurrentLocation(getString(row, "workplace_city"));
        facebookUser.setHometown(getString(row, "birth_place"));
        facebookUser.setWorkplace(getString(row, "work_place"));
        facebookUser.setRelationshipStatus(getString(row, "marital_status"));
        facebookUser.setDate(getString(row, "birth_date"));
        facebookUser.setJoinDate(getString(row, "join_date"));
        facebookUser.setMysqlTable(mysqlTable);

        // 可以根据需要添加更多字段映射

        return facebookUser;
    }

    /**
     * 安全地从Map中获取字符串值
     *
     * @param row 数据行
     * @param key 键名
     * @return 字符串值
     */
    private String getString(Map<String, Object> row, String key) {
        Object value = row.get(key);
        return value != null ? value.toString() : null;
    }

    // ==================== 两阶段查询实现 ====================

    @Override
    public FacebookUser matchByNameAndWorkplaceTwoStage(String firstName, String lastName, String workplace) {
        log.info("两阶段查询 - 姓名+工作地点匹配: firstName={}, lastName={}, workplace={}",
                firstName, lastName, workplace);

        try {
            // 第一阶段：ES快速检索
            List<Map<String, String>> esResults = facebookEsService.searchByNameAndWorkplace(
                firstName, lastName, workplace, 50);

            if (esResults.isEmpty()) {
                log.info("ES查询未找到匹配记录");
                return null;
            }

            // 第二阶段：MySQL精确查询
            List<FacebookUser> mysqlResults = batchQueryFromMysql(esResults);

            // 返回第一个匹配结果（可以根据业务需求调整）
            return mysqlResults.isEmpty() ? null : mysqlResults.get(0);

        } catch (Exception e) {
            log.error("两阶段查询失败 - 姓名+工作地点: firstName={}, lastName={}, workplace={}, error={}",
                     firstName, lastName, workplace, e.getMessage(), e);

            // 降级策略：使用传统MySQL查询
            return fallbackToMysqlQuery(firstName, lastName, workplace, "name_workplace");
        }
    }

    @Override
    public FacebookUser matchPhoneByNameAndWorkplaceTwoStage(String firstName, String lastName, String workplace) {
        log.info("两阶段查询 - 姓名+工作地点匹配手机号: firstName={}, lastName={}, workplace={}",
                firstName, lastName, workplace);

        try {
            // 第一阶段：ES快速检索
            List<Map<String, String>> esResults = facebookEsService.searchByNameAndWorkplace(
                firstName, lastName, workplace, 50);

            if (esResults.isEmpty()) {
                log.info("ES查询未找到匹配记录");
                return null;
            }

            // 第二阶段：MySQL精确查询，筛选有手机号的记录
            List<FacebookUser> mysqlResults = batchQueryFromMysql(esResults);

            // 返回第一个有手机号的匹配结果
            return mysqlResults.stream()
                    .filter(user -> user.getPhoneNumber() != null && !user.getPhoneNumber().trim().isEmpty())
                    .findFirst()
                    .orElse(null);

        } catch (Exception e) {
            log.error("两阶段查询失败 - 姓名+工作地点匹配手机号: firstName={}, lastName={}, workplace={}, error={}",
                     firstName, lastName, workplace, e.getMessage(), e);

            // 降级策略：使用传统MySQL查询
            return fallbackToMysqlQuery(firstName, lastName, workplace, "phone_by_name_workplace");
        }
    }

    @Override
    public FacebookUser matchPhoneByNameAndEmailTwoStage(String firstName, String lastName, String email) {
        log.info("两阶段查询 - 姓名+邮箱匹配手机号: firstName={}, lastName={}, email={}",
                firstName, lastName, email);

        try {
            // 第一阶段：ES快速检索
            List<Map<String, String>> esResults = facebookEsService.searchByNameAndEmail(
                firstName, lastName, email, 50);

            if (esResults.isEmpty()) {
                log.info("ES查询未找到匹配记录");
                return null;
            }

            // 第二阶段：MySQL精确查询，筛选有手机号的记录
            List<FacebookUser> mysqlResults = batchQueryFromMysql(esResults);

            // 返回第一个有手机号的匹配结果
            return mysqlResults.stream()
                    .filter(user -> user.getPhoneNumber() != null && !user.getPhoneNumber().trim().isEmpty())
                    .findFirst()
                    .orElse(null);

        } catch (Exception e) {
            log.error("两阶段查询失败 - 姓名+邮箱匹配手机号: firstName={}, lastName={}, email={}, error={}",
                     firstName, lastName, email, e.getMessage(), e);

            // 降级策略：使用传统MySQL查询
            return fallbackToMysqlQuery(firstName, lastName, email, "phone_by_name_email");
        }
    }

    @Override
    public FacebookUser matchEmailByPhoneAndNameTwoStage(String phoneNumber, String firstName, String lastName) {
        log.info("两阶段查询 - 手机号+姓名匹配邮箱: phoneNumber={}, firstName={}, lastName={}",
                phoneNumber, firstName, lastName);

        try {
            // 第一阶段：ES快速检索
            List<Map<String, String>> esResults = facebookEsService.searchByPhoneAndName(
                phoneNumber, firstName, lastName, 50);

            if (esResults.isEmpty()) {
                log.info("ES查询未找到匹配记录");
                return null;
            }

            // 第二阶段：MySQL精确查询，筛选有邮箱的记录
            List<FacebookUser> mysqlResults = batchQueryFromMysql(esResults);

            // 返回第一个有邮箱的匹配结果
            return mysqlResults.stream()
                    .filter(user -> user.getEmail() != null && !user.getEmail().trim().isEmpty()
                                   && user.getEmail().contains("@"))
                    .findFirst()
                    .orElse(null);

        } catch (Exception e) {
            log.error("两阶段查询失败 - 手机号+姓名匹配邮箱: phoneNumber={}, firstName={}, lastName={}, error={}",
                     phoneNumber, firstName, lastName, e.getMessage(), e);

            // 降级策略：使用传统MySQL查询
            return fallbackToMysqlQuery(phoneNumber, firstName, lastName, "email_by_phone_name");
        }
    }

    /**
     * 批量从MySQL查询完整的联系人信息
     *
     * @param esResults ES返回的标识信息列表
     * @return 完整的FacebookUser列表
     */
    private List<FacebookUser> batchQueryFromMysql(List<Map<String, String>> esResults) {
        List<FacebookUser> results = new ArrayList<>();

        if (esResults.isEmpty()) {
            return results;
        }

        log.info("开始批量MySQL查询，ES返回{}条记录", esResults.size());

        // 按数据表分组，提升查询效率
        Map<String, List<String>> tableGroups = esResults.stream()
                .collect(Collectors.groupingBy(
                    item -> item.get("mysqlTable"),
                    Collectors.mapping(item -> item.get("id"), Collectors.toList())
                ));

        for (Map.Entry<String, List<String>> entry : tableGroups.entrySet()) {
            String tableName = entry.getKey();
            List<String> ids = entry.getValue();

            try {
                List<FacebookUser> tableResults = batchQueryFromSingleTable(tableName, ids);
                results.addAll(tableResults);
                log.debug("从表{}查询到{}条记录", tableName, tableResults.size());

            } catch (Exception e) {
                log.error("从表{}批量查询失败: ids={}, error={}", tableName, ids, e.getMessage(), e);
            }
        }

        log.info("批量MySQL查询完成，共获取{}条完整记录", results.size());
        return results;
    }

    /**
     * 从单个数据表批量查询记录
     *
     * @param tableName 数据表名
     * @param ids ID列表
     * @return FacebookUser列表
     */
    private List<FacebookUser> batchQueryFromSingleTable(String tableName, List<String> ids) {
        List<FacebookUser> results = new ArrayList<>();
        jdbcTemplate.setDataSource(faceBookDataSource.dataSource());

        if (ids.isEmpty() || !isValidTableName(tableName)) {
            return results;
        }

        try {
            // 构建批量查询SQL，使用IN子句
            String placeholders = ids.stream().map(id -> "?").collect(Collectors.joining(","));
            String sql = "SELECT * FROM " + tableName + " WHERE id IN (" + placeholders + ")";

            log.debug("执行批量查询: table={}, sql={}, ids={}", tableName, sql, ids);

            // 执行查询
            List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, ids.toArray());

            // 转换结果
            for (Map<String, Object> row : rows) {
                FacebookUser user = convertMapToFacebookUser(row, tableName);
                if (user != null) {
                    results.add(user);
                }
            }

            log.debug("表{}批量查询完成: 查询{}个ID，返回{}条记录", tableName, ids.size(), results.size());

        } catch (Exception e) {
            log.error("表{}批量查询失败: ids={}, error={}", tableName, ids, e.getMessage(), e);
        }

        return results;
    }

    /**
     * 降级策略：当ES查询失败时使用传统MySQL查询
     *
     * @param param1 参数1
     * @param param2 参数2
     * @param param3 参数3
     * @param queryType 查询类型
     * @return FacebookUser
     */
    private FacebookUser fallbackToMysqlQuery(String param1, String param2, String param3, String queryType) {
        log.warn("启用降级策略，使用传统MySQL查询: queryType={}, param1={}, param2={}, param3={}",
                queryType, param1, param2, param3);

        try {
            switch (queryType) {
                case "name_workplace":
                    return matchByNameAndWorkplace(param1, param2, param3);
                case "phone_by_name_workplace":
                    return matchPhoneByNameAndWorkplace(param1, param2, param3);
                case "phone_by_name_email":
                    return matchPhoneByNameAndEmail(param1, param2, param3);
                case "email_by_phone_name":
                    return matchEmailByPhoneAndName(param1, param2, param3);
                default:
                    log.warn("未知的查询类型: {}", queryType);
                    return null;
            }
        } catch (Exception e) {
            log.error("降级策略查询也失败: queryType={}, error={}", queryType, e.getMessage(), e);
            return null;
        }
    }

    // ==================== 导出功能两阶段查询实现 ====================

    @Override
    public List<FacebookUser> exportFacebookDataTwoStage(FacebookQueryDTO queryDTO) {
        log.info("两阶段查询 - 导出数据: queryDTO={}", queryDTO);

        try {
            // 第一阶段：ES快速检索
            int limit = queryDTO.getLimit() != null ? queryDTO.getLimit() : 100000; // 默认最多10万条
            List<Map<String, String>> esResults = facebookEsService.searchForExport(queryDTO, limit);

            if (esResults.isEmpty()) {
                log.info("ES查询未找到匹配记录");
                return new ArrayList<>();
            }

            log.info("ES查询返回{}条记录，开始MySQL批量查询", esResults.size());

            // 第二阶段：MySQL精确查询
            List<FacebookUser> mysqlResults = batchQueryFromMysql(esResults);

            log.info("两阶段导出查询完成，返回{}条记录", mysqlResults.size());
            return mysqlResults;

        } catch (Exception e) {
            log.error("两阶段导出查询失败: queryDTO={}, error={}", queryDTO, e.getMessage(), e);

            // 降级策略：使用传统导出方法
            return fallbackToTraditionalExport(queryDTO);
        }
    }

    @Override
    public long getExportDataCountTwoStage(FacebookQueryDTO queryDTO) {
        log.info("两阶段查询 - 获取导出数据总数: queryDTO={}", queryDTO);

        try {
            // 使用ES快速计数
            long count = facebookEsService.countForExport(queryDTO);
            log.info("ES计数查询完成，匹配记录总数: {}", count);
            return count;

        } catch (Exception e) {
            log.error("两阶段计数查询失败: queryDTO={}, error={}", queryDTO, e.getMessage(), e);

            // 降级策略：使用传统计数方法
            return fallbackToTraditionalCount(queryDTO);
        }
    }

    @Override
    public List<FacebookUser> exportFacebookDataTwoStageWithPagination(FacebookQueryDTO queryDTO, int pageNum, int pageSize) {
        log.info("两阶段查询 - 分页导出数据: queryDTO={}, pageNum={}, pageSize={}", queryDTO, pageNum, pageSize);

        try {
            // 计算分页参数
            int from = (pageNum - 1) * pageSize;

            // 第一阶段：ES分页检索
            List<Map<String, String>> esResults = facebookEsService.searchForExportWithPagination(queryDTO, from, pageSize);

            if (esResults.isEmpty()) {
                log.info("ES分页查询未找到匹配记录");
                return new ArrayList<>();
            }

            log.info("ES分页查询返回{}条记录，开始MySQL批量查询", esResults.size());

            // 第二阶段：MySQL精确查询
            List<FacebookUser> mysqlResults = batchQueryFromMysql(esResults);

            log.info("两阶段分页导出查询完成，返回{}条记录", mysqlResults.size());
            return mysqlResults;

        } catch (Exception e) {
            log.error("两阶段分页导出查询失败: queryDTO={}, pageNum={}, pageSize={}, error={}",
                     queryDTO, pageNum, pageSize, e.getMessage(), e);

            // 降级策略：使用传统分页导出方法
            return fallbackToTraditionalPaginationExport(queryDTO, pageNum, pageSize);
        }
    }

    @Override
    public List<FacebookUser> searchFacebookUsersWithEmail(String searchKeyword, int offset, int size) {
        List<FacebookUserEs> facebookUserEsList = facebookEsService.searchFacebookUsersWithEmail(searchKeyword, offset, size);

        List<FacebookUser> facebookUserList = new ArrayList<>();
        for (FacebookUserEs faceEs : facebookUserEsList) {
            FacebookUser facebookDetailByTableAndId = getFacebookDetailByTableAndId(faceEs.getMysqlTable(), faceEs.getMysqlId());
            facebookUserList.add(facebookDetailByTableAndId);
        }
        return facebookUserList;

    }

    @Override
    public Long countFacebookUsersWithEmail(String searchKeyword) {

        return facebookEsService.countFacebookUsersWithEmail(searchKeyword);

    }

    @Override
    public List<FacebookUser> searchFacebookUsersWithPhone(String searchKeyword, int offset, int size) {
        List<FacebookUserEs> facebookUserEsList = facebookEsService.searchFacebookUsersWithPhone(searchKeyword, offset, size);

        List<FacebookUser> facebookUserList = new ArrayList<>();
        for (FacebookUserEs faceEs : facebookUserEsList) {
            FacebookUser facebookDetailByTableAndId = getFacebookDetailByTableAndId(faceEs.getMysqlTable(), faceEs.getMysqlId());
            facebookUserList.add(facebookDetailByTableAndId);
        }
        return facebookUserList;
    }

    @Override
    public Long countFacebookUsersWithPhone(String searchKeyword) {
        return facebookEsService.countFacebookUsersWithPhone(searchKeyword);
    }

    @Override
    public List<FacebookUser> findByEmail(String searchEmail) {
        List<Map<String, String>> maps = facebookEsService.searchByEmail(searchEmail, 10, 0);

        List<FacebookUser> users = new ArrayList<>();

        for (Map<String, String> map : maps) {
            String mysqlTable = map.get("mysqlTable");
            String id = map.get("id");
            FacebookUser facebookDetailByTableAndId = getFacebookDetailByTableAndId(mysqlTable, id);
            users.add(facebookDetailByTableAndId);
        }

        return users;
    }

    @Override
    public List<FacebookUser> findByPhone(String phone) {
        List<Map<String, String>> maps = facebookEsService.searchByEmail(phone, 10, 0);

        List<FacebookUser> users = new ArrayList<>();

        for (Map<String, String> map : maps) {
            String mysqlTable = map.get("mysqlTable");
            String id = map.get("id");
            FacebookUser facebookDetailByTableAndId = getFacebookDetailByTableAndId(mysqlTable, id);
            users.add(facebookDetailByTableAndId);
        }

        return users;
    }

    @Override
    public List<Map<String, Object>> searchByEmail(String email, int size, int offset) {
        List<Map<String, String>> maps = facebookEsService.searchByEmail(email, size, offset);

        List<Map<String, Object>> users = new ArrayList<>();

        for (Map<String, String> map : maps) {
            String mysqlTable = map.get("mysqlTable");
            String id = map.get("id");
            Map<String, Object>facebookByTableAndId = getFacebookByTableAndId(mysqlTable, id);
            users.add(facebookByTableAndId);
        }

        return users;
    }

    @Override
    public List<Map<String, Object>> searchByPhone(String phone, int size, int offset) {
        List<Map<String, String>> maps = facebookEsService.searchByPhone(phone, size, offset);

        List<Map<String, Object>> users = new ArrayList<>();

        for (Map<String, String> map : maps) {
            String mysqlTable = map.get("mysqlTable");
            String id = map.get("id");
            Map<String, Object>facebookByTableAndId = getFacebookByTableAndId(mysqlTable, id);
            users.add(facebookByTableAndId);
        }

        return users;
    }

    @Override
    public long countByEmail(String email) {
        return facebookEsService.countByEmail(email);
    }

    @Override
    public long countByPhone(String phone) {
        return facebookEsService.countByPhone(phone);
    }

    @Override
    public List<String> searchCurrent(String current) {
        return facebookEsService.searchCurrent(current);
    }

    @Override
    public List<String> searchHometown(String hometowm) {
        return facebookEsService.searchHometown(hometowm);
    }

    @Override
    public List<String> searchWorkplaces(String workplace) {
        return facebookEsService.searchWorkplaces(workplace);
    }

    @Override
    public List<FacebookUser> searchFacebookData(String companyName, String fullName, Integer matchType) {
        log.info("批量搜索: 公司名称={}，人名={}", companyName,fullName);
        try {
            // es搜索
            List<FacebookUserEs> facebookUserEsList;
            if (matchType == 1) {
                facebookUserEsList = facebookEsService.searchFacebookDataWithPhone(companyName, fullName);
            }else {
                facebookUserEsList = facebookEsService.searchFacebookDataWithEmail(companyName, fullName);
            }

            log.info("ES查询到{}条Facebook数据，开始从MySQL批量获取详细信息", facebookUserEsList.size());

            if (facebookUserEsList.isEmpty()){
                return new ArrayList<>();
            }

            // 按表名分组
            Map<String, List<String>> tableToIds = new HashMap<>();
            for (FacebookUserEs faceEs : facebookUserEsList) {
                String table = faceEs.getMysqlTable();
                String id = faceEs.getMysqlId();
                if (table != null && id != null) {
                    tableToIds.computeIfAbsent(table, k -> new ArrayList<>()).add(id);
                }
            }
            
            log.info("数据分布在{}个不同的表中，开始并行批量查询", tableToIds.size());

            // 使用线程池并行查询不同的表
            ExecutorService queryExecutor = Executors.newFixedThreadPool(Math.min(tableToIds.size(), 20));
            List<CompletableFuture<List<FacebookUser>>> queryTasks = new ArrayList<>();
            
            for (Map.Entry<String, List<String>> entry : tableToIds.entrySet()) {
                String table = entry.getKey();
                List<String> ids = entry.getValue();
                
                CompletableFuture<List<FacebookUser>> task = CompletableFuture.supplyAsync(() -> {
                    try {
                        return batchGetFacebookDetailByTableAndIds(table, ids);
                    } catch (Exception e) {
                        log.error("批量查询表{}失败，包含{}条记录", table, ids.size(), e);
                        return new ArrayList<>();
                    }
                }, queryExecutor);
                
                queryTasks.add(task);
            }
            
            // 等待所有查询完成
            CompletableFuture<Void> allTasks = CompletableFuture.allOf(
                queryTasks.toArray(new CompletableFuture[0])
            );
            allTasks.join();
            
            // 合并结果
            List<FacebookUser> facebookUserList = new ArrayList<>();
            for (CompletableFuture<List<FacebookUser>> task : queryTasks) {
                try {
                    List<FacebookUser> results = task.get();
                    facebookUserList.addAll(results);
                } catch (Exception e) {
                    log.error("获取查询结果失败", e);
                }
            }
            
            // 关闭线程池
            queryExecutor.shutdown();
            
            int successCount = facebookUserList.size();
            int failCount = facebookUserEsList.size() - successCount;
            
            log.info("Facebook数据处理完成: ES查询{}条, MySQL成功获取{}条, 失败{}条", 
                     facebookUserEsList.size(), successCount, failCount);
            return facebookUserList;


        }catch (Exception e){
            log.error("Facebook库查询失败", e);
            throw new RuntimeException("Facebook库查询失败", e);
        }
    }

    /**
     * 降级策略：使用传统导出方法
     *
     * @param queryDTO 查询条件
     * @return FacebookUser列表
     */
    private List<FacebookUser> fallbackToTraditionalExport(FacebookQueryDTO queryDTO) {
        log.warn("启用导出降级策略，使用传统MySQL查询: queryDTO={}", queryDTO);

        try {
            return exportFacebookData(queryDTO);
        } catch (Exception e) {
            log.error("导出降级策略也失败: queryDTO={}, error={}", queryDTO, e.getMessage(), e);
            return new ArrayList<>();
        }
    }

    /**
     * 降级策略：使用传统计数方法
     *
     * @param queryDTO 查询条件
     * @return 记录总数
     */
    private long fallbackToTraditionalCount(FacebookQueryDTO queryDTO) {
        log.warn("启用计数降级策略，使用传统MySQL查询: queryDTO={}", queryDTO);

        try {
            // 这里需要实现传统的计数查询，暂时返回导出数据的大小
            List<FacebookUser> results = exportFacebookData(queryDTO);
            return results.size();
        } catch (Exception e) {
            log.error("计数降级策略也失败: queryDTO={}, error={}", queryDTO, e.getMessage(), e);
            return 0;
        }
    }

    /**
     * 降级策略：使用传统分页导出方法
     *
     * @param queryDTO 查询条件
     * @param pageNum 页码
     * @param pageSize 每页大小
     * @return FacebookUser列表
     */
    private List<FacebookUser> fallbackToTraditionalPaginationExport(FacebookQueryDTO queryDTO, int pageNum, int pageSize) {
        log.warn("启用分页导出降级策略，使用传统MySQL查询: queryDTO={}, pageNum={}, pageSize={}",
                queryDTO, pageNum, pageSize);

        try {
            // 设置分页参数
            queryDTO.setPageNum(pageNum);
            queryDTO.setPageSize(pageSize);
            queryDTO.setLimit(pageSize);

            return exportFacebookData(queryDTO);
        } catch (Exception e) {
            log.error("分页导出降级策略也失败: queryDTO={}, pageNum={}, pageSize={}, error={}",
                     queryDTO, pageNum, pageSize, e.getMessage(), e);
            return new ArrayList<>();
        }
    }

}